'''
Author: china-fanxin
WibeSite: https://www.fan-xin.cn
LastEditTime: 2022-09-27 09:54:14
Description: business logic code
'''

import os,sqlparse
import pandas as pd 
from replaceWordsUI import Ui_replaceWordsUI
from PyQt5.QtWidgets import QMainWindow,QFileDialog,QMessageBox,QApplication
import frozen_dir

class Mywindow(QMainWindow,Ui_replaceWordsUI):
    
    def __init__(self,parent = None):
        QMainWindow.__init__(self,parent)
        self.setupUi(self)
        self.selectConfigFileBt.clicked.connect(self.open_file)
        self.transformBt.clicked.connect(self.transform_content)
        self.transformAndFormatBt.clicked.connect(self.transform_format_content)
        self.formatBt.clicked.connect(self.format_sql)
        self.clearInputAreaBt.clicked.connect(self.clear_input_area)
        self.copyOutputAreaBt.clicked.connect(self.copy_output_area)
        self.config_file_path = frozen_dir.app_path()+r'\替换映射关系.xlsx'
           
    def open_file(self):
        '''
        open file and get the file path to global varible
        '''
        self.config_file_path = QFileDialog.getOpenFileName(self, "选取文件", os.getcwd(),"Excel files(*.xlsx , *.xls)")[0]
        print(self.config_file_path)

    def replace_word(self,content:str,search_text:str,replace_text:str)->str:
        '''
        replace a word 
        '''
        return content.upper().replace(search_text.upper(),replace_text.upper())
    
    def replace_batch(self,content:str,map_dict:dict)->str:
        '''
        Batch replacement content
        '''
        for k,v in map_dict.items() :
            search_text = k
            replace_text = v
            content = self.replace_word(content,search_text,replace_text)
        return content

    def read_excel_to_dict(self,file_path:str,col1:str,col2:str)->dict :
        '''
        read data from Excel file and convert to dict config
        '''
        try:
            df = pd.read_excel(file_path,dtype={col1:str,col2:str})
            df = df.dropna(axis=0,how='any',inplace=False)
            print(df)
            print(dict(zip(df[col1],df[col2])))
            return dict(zip(df[col1],df[col2]))
        except :
            print("配置文件有问题")
            pass

    def transform_content(self):
        '''
        transform content by rules and output 
        '''
        print("所选配置文件是",self.config_file_path)
        input_content = self.get_content()
        try:
            output_content = self.replace_batch(input_content,self.read_excel_to_dict(self.config_file_path,'查找内容','替换内容'))
            self.set_ouput_area(output_content)
            self.copy_text_clipboard(output_content)
        except:
            self.msg_info("请选择正确的映射关系文件！")
            return 

    def transform_format_content(self):
        '''
        transform and format content by rules and output 
        '''
        global config_file_path
        print("所选配置文件是",config_file_path)
        input_content = self.get_content()
        try:
            output_content = sqlparse.format(self.replace_batch(input_content,self.read_excel_to_dict(self.config_file_path,'查找内容','替换内容')),reindent=True, keyword_case='upper')
            self.set_ouput_area(output_content)
            self.copy_text_clipboard(output_content)
        except:
            self.msg_info("请选择正确的映射关系文件！")
            pass

    def format_sql(self)->str:
        '''
        format the content which write by SQL
        '''
        input_content = self.get_content()
        output_content = sqlparse.format(input_content,reindent=True, keyword_case='upper')
        self.set_ouput_area(output_content)
        self.copy_text_clipboard(output_content)
        
    def clear_input_area(self):
        '''
        clear the input area by clicked button
        '''
        self.inputArea.clear()
    
    def copy_output_area(self):
        '''
        copy output area's content by clicked the button
        '''
        self.copy_text_clipboard(self.outputArea.toPlainText())

    def get_content(self) ->str:
        '''
        get the content on input area
        '''
        return self.inputArea.toPlainText()

    def set_ouput_area(self,content):
        '''
        set output area content with transformed content
        '''
        self.outputArea.setText(content)


    def msg_info(self,msg:str):
        '''
        Prompt the user to select a configuration file
        '''
        QMessageBox.information(self,"小鑫提示：",msg)

    def copy_text_clipboard(self,text):
        '''
        copy the content to clipboard by clicked the button
        '''
        clipboard = QApplication.clipboard()
        clipboard.setText(text)
        self.msg_info("已复制到剪切板！")